/**
 * 
 */
package com.jeecms.wicool.dao.report.impl;

import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.hibernate.transform.ResultTransformer;
import org.springframework.stereotype.Repository;

import com.jeecms.cms.entity.main.CmsUser;
import com.jeecms.common.hibernate3.HibernateBaseDao;
import com.jeecms.wicool.dao.report.ReportDao;
import com.jeecms.wicool.entity.product.Product;

/**
 * @author spring
 *
 */
@Repository
public class ReportDaoImpl extends HibernateBaseDao<Product, Integer> implements ReportDao {

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String,Object>> getSaleDayReportPage(String saleDate,  CmsUser user) {
		String sql = "select p.product_name,sku.sku_name,p.price,count(o.id) as sale_count,sum(o.order_price) as sale_money_count "
				+ " from product p "
				+ " inner join product_sku sku on p.id=sku.product_id "
				+ " left join order_product_mapping opm on p.id=opm.product_id and sku.id=opm.sku_id "
				+ " left join orders o on o.id=opm.order_id "
				+ " where p.status=1 and o.status = 1 ";
		if(StringUtils.isNotBlank(saleDate)){
			sql += " and  date_format(o.create_time,'%Y-%m-%d') = '"+saleDate+"'";
		}
		sql +=  " group by p.product_name,sku.sku_name,p.price "
			 + " order by count(o.id) desc";
		Query query1 = getSession().createSQLQuery(sql);
		setResultTransformer(query1);
		List<Map<String,Object>> list = query1.list();
		if(list != null && !list.isEmpty()){
			BigDecimal sum = new BigDecimal(0);
			for (int i = 0; i < list.size(); i++) {
				Map<String,Object> map = list.get(i);
				map.put("num", i+1);
				Double money = map.get("sale_money_count") == null ? 0 : Double.valueOf(map.get("sale_money_count").toString());
				sum = sum.add(BigDecimal.valueOf(money));
			}
			Map<String, Object> map = new HashMap<String, Object>(1);
			map.put("sale_count", "合计：");
			map.put("sale_money_count", sum.doubleValue());
			list.add(map);
		}
		return list;
	}

	@SuppressWarnings("unchecked")
	public Map<String,Object> getSaleDayReportPayment(String saleDate, CmsUser user){
		String sql = "select sum(case when o.payway=1 then o.order_price  else 0 end) huodao_pay_money, "
				+ " sum(case when o.payway=2 then o.order_price  else 0 end) weixin_pay_money, "
				+ " sum(case when o.payway=3 then o.order_price  else 0 end) balance_pay_money, "
				+ " sum(o.coupon)  youhui_pay_money "
				+ " from product p "
				+ " inner join product_sku sku on p.id=sku.product_id "
				+ " left join order_product_mapping opm on p.id=opm.product_id and sku.id=opm.sku_id "
				+ " left join orders o on o.id=opm.order_id "
				+ " where p.status=1 and o.status = 1 ";
		if(StringUtils.isNotBlank(saleDate)){
			sql += " and  date_format(o.create_time,'%Y-%m-%d') = '"+saleDate+"'";
		}
		Query query1 = getSession().createSQLQuery(sql);
		setResultTransformer(query1);
		List<Map<String,Object>> list = query1.list();
		if(list != null && !list.isEmpty()){
			return list.get(0);
		}
		return new HashMap<String, Object>();
	}
	
	@Override
	public List<Map<String,Object>> getSaleMonthReportPage(String beginDate, String endDate, CmsUser user) {
		String sql = "select p.product_name,sku.sku_name,p.price,count(o.id) as sale_count,sum(o.order_price) as sale_money_count "
				+ " from product p "
				+ " inner join product_sku sku on p.id=sku.product_id "
				+ " left join order_product_mapping opm on p.id=opm.product_id and sku.id=opm.sku_id "
				+ " left join orders o on o.id=opm.order_id "
				+ " where p.status=1 and o.status = 1 ";
		if(StringUtils.isNotBlank(beginDate)){
			sql += " and  date_format(o.create_time,'%Y-%m-%d') >= '"+beginDate+"'";
		}
		if(StringUtils.isNotBlank(endDate)){
			sql += " and  date_format(o.create_time,'%Y-%m-%d') <= '"+endDate+"'";
		}
		sql +=  " group by p.product_name,sku.sku_name,p.price "
			 + " order by count(o.id) desc";
		Query query1 = getSession().createSQLQuery(sql);
		setResultTransformer(query1);
		List<Map<String,Object>> list = query1.list();
		if(list != null && !list.isEmpty()){
			BigDecimal sum = new BigDecimal(0);
			for (int i = 0; i < list.size(); i++) {
				Map<String,Object> map = list.get(i);
				map.put("num", i+1);
				Double money = map.get("sale_money_count") == null ? 0 : Double.valueOf(map.get("sale_money_count").toString());
				sum = sum.add(BigDecimal.valueOf(money));
			}
			Map<String, Object> map = new HashMap<String, Object>(1);
			map.put("sale_count", "合计：");
			map.put("sale_money_count", sum.doubleValue());
			list.add(map);
		}
		return list;
	}
	
	public Map<String,Object> getSaleMonthReportPayment(String beginDate,String endDate, CmsUser user){
		String sql = "select sum(case when o.payway=1 then o.order_price  else 0 end) huodao_pay_money, "
				+ " sum(case when o.payway=2 then o.order_price  else 0 end) weixin_pay_money, "
				+ " sum(case when o.payway=3 then o.order_price  else 0 end) balance_pay_money, "
				+ " sum(o.coupon)  youhui_pay_money "
				+ " from product p "
				+ " inner join product_sku sku on p.id=sku.product_id "
				+ " left join order_product_mapping opm on p.id=opm.product_id and sku.id=opm.sku_id "
				+ " left join orders o on o.id=opm.order_id "
				+ " where p.status=1 and o.status = 1 ";
		if(StringUtils.isNotBlank(beginDate)){
			sql += " and  date_format(o.create_time,'%Y-%m-%d') >= '"+beginDate+"'";
		}
		if(StringUtils.isNotBlank(endDate)){
			sql += " and  date_format(o.create_time,'%Y-%m-%d') <= '"+endDate+"'";
		}
		Query query1 = getSession().createSQLQuery(sql);
		setResultTransformer(query1);
		List<Map<String,Object>> list = query1.list();
		if(list != null && !list.isEmpty()){
			return list.get(0);
		}
		return new HashMap<String, Object>();
	}

	
	public List<Map<String,Object>> getDisSalePrice(Integer disAddId, String startTimeDate, String endTimeDate, Integer typeId){
		String sql = "select dis.address,p.product_name,sku.sku_name,opm.product_price,sum(opm.buy_count) as sale_count,opm.product_price*sum(opm.buy_count) as sale_money_count "
				+ " from product p "
				+ " inner join product_sku sku on p.id=sku.product_id "
				+ " left join order_product_mapping opm on p.id=opm.product_id and sku.id=opm.sku_id "
				+ " left join orders o on o.id=opm.order_id "
				+ " left join distribution_address dis on dis.id=o.distAddrId "
				+ " where p.product_cate_id != -1 and p.status=1 and o.status > 0 ";
		if(disAddId != null){
			sql += " and  o.distAddrId = "+disAddId;
		}
		if(StringUtils.isNotBlank(startTimeDate)){
			sql += " and  date_format(o.create_time,'%Y-%m-%d') >= '"+startTimeDate+"'";
		}
		if(StringUtils.isNotBlank(endTimeDate)){
			sql += " and  date_format(o.create_time,'%Y-%m-%d') <= '"+endTimeDate+"'";
		}
		if(typeId != null){
			sql += " and  o.type_id = "+typeId;
		}
		sql +=  " group by dis.address,p.product_name,sku.sku_name,opm.product_price "
			 + " order by sum(opm.buy_count) desc";
		Query query1 = getSession().createSQLQuery(sql);
		setResultTransformer(query1);
		List<Map<String,Object>> list = query1.list();
		if(list != null && !list.isEmpty()){
			BigDecimal sum = new BigDecimal(0);
			BigDecimal count = new BigDecimal(0);
			for (int i = 0; i < list.size(); i++) {
				Map<String,Object> map = list.get(i);
				map.put("num", i+1);
				Double money = map.get("sale_money_count") == null ? 0 : Double.valueOf(map.get("sale_money_count").toString());
				sum = sum.add(BigDecimal.valueOf(money));
				
				Integer count1 = map.get("sale_count") == null ? 0 : Integer.valueOf(map.get("sale_count").toString());
				count = count.add(BigDecimal.valueOf(count1));
			}
			Map<String, Object> map = new HashMap<String, Object>(1);
			map.put("product_price", "合计：");
			map.put("sale_count", count.intValue());
			map.put("sale_money_count", sum.doubleValue());
			list.add(map);
		}
		return list;
	}
	
	
	public  List<Map<String,Object>> getUserScoreList(String userName ,String mobile,String endTime,String zhuanUserName,String zhuanMobile)
	{
		String sql = "select u.scene_id,u.name,u.mobile,o.sale_money_count, st.score_count"
				+ " from user_info u "
				+ " left join user_info u1 on u.scene_id=u1.id "
				+ " left join ("
				+ "			select st1.user_id,SUM(CASE WHEN st1.trade_type=1 then st1.score_num else -st1.score_num end) score_count "
				+ "			from score_trade st1 "
				+ "			where  date_format(st1.trade_time,'%Y-%m-%d') <= '"+endTime+"'"
				+"			group by st1.user_id "
				+ "  ) st on u.id=st.user_id "
				+ " left join ("
				+ "			select o1.user_id,sum(o1.order_price) as sale_money_count "
				+ "			from orders o1 "
				+ "			where o1.status > 0 AND date_format(o1.create_time,'%Y-%m-%d') <= '"+endTime+"'"
				+ "			group by o1.user_id "
				+ ") o on u.id=o.user_id "
				+ " where 1=1 ";
		if(StringUtils.isNotBlank(userName)){
			sql += " and  u.name like '%"+userName+"%'";
		}
		if(StringUtils.isNotBlank(mobile)){
			sql += " and  u.mobile = '"+mobile+"'";
		}
//		if(StringUtils.isNotBlank(endTime)){
//			sql += " and  (date_format(o.create_time,'%Y-%m-%d') <= '"+endTime+"' or  date_format(st.trade_time,'%Y-%m-%d') <= '"+endTime+"')";
//		}
		if(StringUtils.isNotBlank(zhuanUserName)){
			sql += " and  u1.name like '%"+zhuanUserName+"%'";
		}
		if(StringUtils.isNotBlank(zhuanMobile)){
			sql += " and  u1.mobile = '"+zhuanMobile+"'";
		}
//		sql +=  " group by u.scene_id,u.name,u.mobile ";
		Query query1 = getSession().createSQLQuery(sql);
		setResultTransformer(query1);
		List<Map<String,Object>> list = query1.list();
		
		String sql1 = "select u.id,u.name,u.mobile, SUM(CASE WHEN st.trade_type=1 then st.score_num else -st.score_num end) score_count1"
				+ " from user_info u "
				+ " left join score_trade st on u.id=st.user_id "
				+ " where 1=1 ";
		if(StringUtils.isNotBlank(endTime)){
			sql1 += " and  date_format(st.trade_time,'%Y-%m-%d') <= '"+endTime+"'";
		}
		if(StringUtils.isNotBlank(zhuanUserName)){
			sql1 += " and  u.name like '%"+zhuanUserName+"%'";
		}
		if(StringUtils.isNotBlank(zhuanMobile)){
			sql1 += " and  u.mobile = '"+zhuanMobile+"'";
		}
		sql1 +=  " group by u.id,u.name,u.mobile ";
		
		Query query = getSession().createSQLQuery(sql1);
		setResultTransformer(query);
		List<Map<String,Object>> list1 = query.list();
		
		
		if(list != null && !list.isEmpty() && list1 != null && !list1.isEmpty()){
			for (int i = 0; i < list.size(); i++) {
				Map<String,Object> mapi = list.get(i);
				Integer idi = mapi.get("scene_id") == null ? null : Integer.valueOf(mapi.get("scene_id").toString());
				mapi.put("num", i+1);
				for(int j=0 ; j < list1.size() ; j++){
					Map<String,Object> mapj = list1.get(j);
					Integer idj = mapj.get("id") == null ? 0 : Integer.valueOf(mapj.get("id").toString());
					if(idj.equals(idi)){
						mapi.put("zhuanName", mapj.get("name"));
						mapi.put("zhuanMobile", mapj.get("mobile"));
						mapi.put("score_count1", mapj.get("score_count1"));
						continue;
					}
				}
			}
		}
		
		return list;
	}
	
    @Override
	protected Class<Product> getEntityClass() {
		return null;
	}

	private void setResultTransformer(Query query){
		query.setResultTransformer(new ResultTransformer() {
	         
			private static final long serialVersionUID = 5327536505724378149L;

			   @Override
			   public Object transformTuple(Object[] values, String[] columns) {
			      Map<String, Object> map = new HashMap<String, Object>(1);
			      int i = 0;
			      for(String column : columns){
			         map.put(column, values[i++]);
			      }
			      return map;
			   }
			   
			   @Override
			   public List transformList(List list) {
			      return list;
			   }
			});
	}
}
